﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.Data.OleDb;
using System.IO;
using System.Collections;

namespace Trade.Library.Common
{
    public static class ExcelImportHelper
    {

        public static string UploadFile(HttpPostedFile file, string uploadpath)
        {
            string fileName = "";
            string strDir = uploadpath;
            if (file.ContentLength > 0)
            {
                if (file.ContentLength < 300 * 200)
                {
                    string strNew = DateTime.Now.ToString("yyyyMMddHHmmssfff") + System.IO.Path.GetExtension(file.FileName);        //新文件名
                    fileName = strDir + strNew;
                    file.SaveAs(fileName);
                    return strDir + strNew;
                }
                else
                {
                    return "";
                }
            }
            else { return ""; }
        }


        public static void DeleteTempFile(string strTempFile)
        {
            try
            {
                if (System.IO.File.Exists(strTempFile))
                {
                    System.IO.File.Delete(strTempFile);
                }
            }
            catch { }
        }
        /// <summary> 
        /// 返回Excel数据源 
        /// </summary> 
        /// <param name="filename">文件路径</param> 
        /// <param name="TSql">TSql</param> 
        /// <returns>DataSet</returns> 
        public static DataTable ExcelPathToDataSet(string strPath, string strTableName)
        {
            try
            {
                DataSet ds;
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;IMEX=1';data source=" + strPath;
                OleDbConnection myConn = new OleDbConnection(strCon);
                string strCom = "select * from [" + strTableName + "$]";
                myConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                ds = new DataSet();
                myCommand.Fill(ds);
                myConn.Close();
                #region #去除空行
                if (ds != null)
                {
                    int iColCount = ds.Tables[0].Columns.Count;
                    bool delFlg = true;
                    for (int i = ds.Tables[0].Rows.Count - 1; i >= 0; i--)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        delFlg = true;
                        for (int j = 0; j < iColCount; j++)
                        {
                            if (!Convert.IsDBNull(dr[j]))
                            {
                                delFlg = false;
                                break;
                            }
                        }
                        if (delFlg)
                        {
                            ds.Tables[0].Rows.Remove(dr);
                        }
                    }
                }
                #endregion
                //DeleteTempFile(strPath);//删除文件
                return ds.Tables[0];
            }
            catch (Exception ee)
            {
                throw ee;
                //return new DataTable();
            }
        }

        /// <summary>
        /// 确保目录存在，不存在则创建，除非无法创建则报错
        /// </summary>
        /// <param name="strPath"></param>
        public static bool DirectoryExist(string strPath)
        {
            try
            {
                if (!Directory.Exists(strPath))
                {
                    Directory.CreateDirectory(strPath);
                }
                return true;
            }
            catch { return false; }
        }

        /// <summary>
        /// csv解析成datatble 
        /// </summary>
        /// <param name="sFile"></param>
        /// <param name="bhead"></param>
        /// <returns></returns>
        public static DataTable CsvReader(string strFile, bool bhead)
        {
            try
            {
                int intColCount = 0;
                bool blnFlag = true;
                DataTable mydt = new DataTable("table");

                DataColumn mydc;
                DataRow mydr;

                string strline;
                string[] aryline;

                using (System.IO.StreamReader mysr = new System.IO.StreamReader(strFile, Encoding.Default))
                {
                    while ((strline = mysr.ReadLine()) != null)
                    {
                        aryline = GetCSVLine(strline);

                        if (blnFlag)
                        {
                            blnFlag = false;
                            intColCount = aryline.Length;
                            for (int i = 0; i < aryline.Length; i++)
                            {
                                mydc = new DataColumn(aryline[i], typeof(System.String));
                                mydt.Columns.Add(mydc);
                            }
                            //如果包含标题，则跳过这一行数据
                            if (bhead)
                            {
                                continue;
                            }
                        }

                        mydr = mydt.NewRow();
                        for (int i = 0; i < intColCount; i++)
                        {
                            mydr[i] = aryline[i];
                        }
                        mydt.Rows.Add(mydr);
                    }
                }
                return mydt;
            }
            catch (Exception ee)
            { throw ee; }
        }

        /// <summary>
        /// 解析一行csv数据，返回字符串数组
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private static string[] GetCSVLine(string data)
        {
            if (data == null) return null;
            if (data.Length == 0) return new string[0];

            ArrayList result = new ArrayList();

            int pos = -1;
            while (pos < data.Length)
                result.Add(ParseCSVField(data, ref pos));

            return (string[])result.ToArray(typeof(string));
        }

        /// <summary>
        /// 逐个解析一行中的数据项
        /// </summary>
        /// <param name="data"></param>
        /// <param name="startSeparatorPosition"></param>
        /// <returns></returns>
        private static string ParseCSVField(string data, ref int startSeparatorPosition)
        {

            if (startSeparatorPosition == data.Length - 1)
            {
                startSeparatorPosition++;
                // 最后一个数据项是空值 
                return "";
            }

            int fromPos = startSeparatorPosition + 1;

            // 处理带引号的数据项
            if (data[fromPos] == '"')
            {
                //如果是引号在最后，则说明数据项只包含引号 
                if (fromPos == data.Length - 1)
                {
                    fromPos++;
                    return "\"";
                }

                // 
                int nextSingleQuote = FindSingleQuote(data, fromPos + 1);
                startSeparatorPosition = nextSingleQuote + 1;
                return data.Substring(fromPos + 1, nextSingleQuote - fromPos - 1).Replace("\"\"", "\"");
            }

            //
            int nextComma = data.IndexOf(',', fromPos);
            if (nextComma == -1)
            {
                startSeparatorPosition = data.Length;
                return data.Substring(fromPos);
            }
            else
            {
                startSeparatorPosition = nextComma;
                return data.Substring(fromPos, nextComma - fromPos);
            }
        }

        /// <summary>
        /// 寻找下一个双引号的位置
        /// </summary>
        /// <param name="data">字符串</param>
        /// <param name="startFrom">其实位置</param>
        /// <returns></returns>
        private static int FindSingleQuote(string data, int startFrom)
        {

            int i = startFrom - 1;
            while (++i < data.Length)
                if (data[i] == '"')
                {
                    // 
                    if (i < data.Length - 1 && data[i + 1] == '"')
                    {
                        i++;
                        continue;
                    }
                    else
                        return i;
                }
            // 
            return i;
        }


    }
}
